<?php
/**
 * Grafik (No.1)
 * Return sumary of the Total Point Per Karyawan
 * @param periodeStart (unix time) - 
 * @param periodeEnd (unix time) - 
 */
function __rep_total_poin_karyawan($karyID, $startMonth, $endMonth, $year){
	$data = array();
	for($month=$startMonth; $month<=$endMonth; $month++){
//		$result = rank_select(false, "a.KODE_KARYAWAN='$karyID' AND year(RANK_PERIODE)='$year' AND month(RANK_PERIODE)='$month'");
//		$tmp = mysql_fetch_assoc($result);
//		$data["$month-$year"] = $tmp['RANK_POINT']? $tmp['RANK_POINT'] : 0;

		$res = perb_select_corelate2(false, 
			"a.KODE_KARYAWAN='$karyID' AND 
				YEAR(a.PERB_TGL) = '$year' AND MONTH(a.PERB_TGL)='$month'", 
			"a.PERB_TGL");
		while ($row = mysql_fetch_assoc($res)){
			$data["$month-$year"] += $row['PERB_POINT'];
		}
	}
	return $data;
}

/**
 * Grafik (No.1 & No.2)
 * Return Rata-rata departemen untuk tiap periode
 * @param periodeStart (unix time) - 
 * @param periodeEnd (unix time) - 
 * @by freezy
 */
function __rep_rata_departemen($departemenID, $startMonth, $endMonth, $year){
	$data = array();
	for($month=$startMonth; $month<=$endMonth; $month++){
		//1. sum the result
		$sql = "SELECT a.*, a1.*
			FROM perbuatan as a, detil_perbuatan as a1, 
				data_karyawan as b, relasi_div_jab_din as c, 
				dep_divisi_jabatan as d, data_department as e
			WHERE a.PERB_NO = a1.PERB_NO AND
				a1.DEPERB_STATUS='2' AND 
				a.KODE_KARYAWAN = b.KODE_KARYAWAN AND
				b.KODE_KARYAWAN  = c.KODE_KARYAWAN AND
				c.ID_DEP_DIV_JAB = d.ID_DEP_DIV_JAB AND
				d.ID_DEPARTMENT  = e.ID_DEPARTMENT AND
				e.ID_DEPARTMENT  = '$departemenID' AND
				year(a.PERB_TGL)='$year' AND month(a.PERB_TGL)='$month'";
		
		$data["$month-$year"] = 0;
		$jmlKaryawan = 0;
		$result = mysql_query($sql);
		$rankList = array();
		while ($tmp = mysql_fetch_assoc($result)){
			if (!in_array($tmp['KODE_KARYAWAN'], $rankList)){
				//load Rank Point 1 periode sebelumnya
//				$rank = rank_select(false, 
//						"a.KODE_KARYAWAN='".$tmp['KODE_KARYAWAN']."' 
//						AND MONTH(a.RANK_PERIODE)=$month-1 
//						AND YEAR(a.RANK_PERIODE)='$year'");
//				$rank = mysql_fetch_assoc($rank);
//				$data["$month-$year"] += $rank['RANK_POINT'];
				
				//marking, sudah di load nilai rangking untuk karyawan ini
				//hitung jumlah karyawan
				$rankList[] = $tmp['KODE_KARYAWAN'];
			}
			$data["$month-$year"] += $tmp['PERB_POINT']? $tmp['PERB_POINT'] : 0;
		}
		
		//2. devide it
		$jmlKaryawan = count($rankList);
//		echo $data["$month-$year"]."/$jmlKaryawan";
		if ($jmlKaryawan) $data["$month-$year"] = $data["$month-$year"]/$jmlKaryawan;
	}
	return $data;
}

function __rep_rata_departemen2($departemenID, $startPeriode, $endPeriode){
	$data = array();
	foreach ($departemenID as $DEPT){
		$result = "SELECT a.*, a1.DEPERB_NO, a1.DETACHIEV_ID, a1.DEPERB_COUNT, 
					a1.DEPERB_STATUS, e.ID_DEPARTMENT, e.NAMA_DEPARTMENT
			FROM perbuatan as a, detil_perbuatan as a1, 
				data_karyawan as b, relasi_div_jab_din as c, 
				dep_divisi_jabatan as d, data_department as e
			WHERE a.PERB_NO = a1.PERB_NO AND
				a1.DEPERB_STATUS='2' AND 
				a.KODE_KARYAWAN = b.KODE_KARYAWAN AND
				b.KODE_KARYAWAN  = c.KODE_KARYAWAN AND
				c.ID_DEP_DIV_JAB = d.ID_DEP_DIV_JAB AND
				d.ID_DEPARTMENT  = e.ID_DEPARTMENT AND
				e.ID_DEPARTMENT = '$DEPT' AND
				DATE_FORMAT(a.PERB_TGL, '%Y-%m') BETWEEN DATE_FORMAT('$startPeriode', '%Y-%m') AND 
												DATE_FORMAT('$endPeriode', '%Y-%m')";
		
		//load nama departemen, untuk key
		$key = mysql_fetch_assoc(departemen_load($DEPT));
		$key = $key['NAMA_DEPARTMENT'];
		
		//1. sum the result
		$result = mysql_query($result);
		$rankList = array();
		while ($tmp = mysql_fetch_assoc($result)){
			$data[$key] += $tmp['PERB_POINT']? $tmp['PERB_POINT'] : 0;
			
			//load Rank Point 1 periode sebelumnya
			if (!in_array($tmp['KODE_KARYAWAN'], $rankList)){
//				$month = date("m",strtotime($startPeriode));
//				$year = date("Y",strtotime($startPeriode));
//				$rank = rank_select(false, 
//						"a.KODE_KARYAWAN='".$tmp['KODE_KARYAWAN']."' 
//						AND MONTH(a.RANK_PERIODE)=$month-1 
//						AND YEAR(a.RANK_PERIODE)='$year'");
//				$rank = mysql_fetch_assoc($rank);
//				$data[$key] += $rank['RANK_POINT'];
				
				//marking, sudah di load nilai rangking untuk karyawan ini
				$rankList[] = $tmp['KODE_KARYAWAN'];
			}
		}
		
		//normalisasi data
		if (!$data[$key]) $data[$key] = 0;	
		
		//2. devide dengan jumlah karyawan
		$jmlKaryawan = count($rankList);
		if ($jmlKaryawan) $data[$key] = $data[$key]/$jmlKaryawan;
	}
	return $data;
}

/**
 * Grafik No.2 
 * Return Rata-rata point keseluruhan
 * @param $startPeriode
 * @param $endPeriode
 * @return unknown_type
 */
function __rep_rata_seluruh_poin($startMonth, $endMonth, $year){
	$data = array();
	for($month=$startMonth; $month<=$endMonth; $month++){
		
		/*
		 * 1. load perbuatan periode saat ini
		 */
		$result = mysql_query(
			"SELECT a.PERB_TGL, a.KODE_KARYAWAN, b.NAMA_KARYAWAN, e.NAMA_DEPARTMENT, a1.DETACHIEV_ID, 
					f.ACHIEV_JENIS, f.ACHIEV_BIDANG, g.DETACHIEV_TINGKAT, g.DETACHIEV_NAMA,
					a1.DEPERB_COUNT, a.PERB_POINT
				FROM perbuatan as a, detil_perbuatan as a1, 
					data_karyawan as b, relasi_div_jab_din as c, 
					dep_divisi_jabatan as d, data_department as e,
					prestasipelanggaran as f, detil_prestasi_pelanggaran as g
				WHERE a.PERB_NO = a1.PERB_NO AND
					a1.DEPERB_STATUS='2' AND 
					a.KODE_KARYAWAN = b.KODE_KARYAWAN AND
					b.KODE_KARYAWAN  = c.KODE_KARYAWAN AND
					c.ID_DEP_DIV_JAB = d.ID_DEP_DIV_JAB AND
					d.ID_DEPARTMENT  = e.ID_DEPARTMENT AND
					f.ACHIEV_ID = g.ACHIEV_ID AND 
					g.DETACHIEV_ID = a1.DETACHIEV_ID AND
					year(a.PERB_TGL)='$year' AND month(a.PERB_TGL)='$month'
			ORDER BY NAMA_DEPARTMENT, NAMA_KARYAWAN, ACHIEV_BIDANG, DEPERB_COUNT");
		
		$rankList = array();
		while ($tmp = mysql_fetch_assoc($result)){
			
			//load Rank Point 1 periode sebelumnya + hitung jumlah karyawan
			if (!in_array($tmp['KODE_KARYAWAN'], $rankList)){
//				$rank = rank_select(false, 
//						"a.KODE_KARYAWAN='".$tmp['KODE_KARYAWAN']."' 
//						AND MONTH(a.RANK_PERIODE)=$month-1 
//						AND YEAR(a.RANK_PERIODE)='$year'");
//				$rank = mysql_fetch_assoc($rank);
//				$data["$month-$year"] += $rank['RANK_POINT'];
				
				//marking, sudah di load nilai rangking untuk karyawan ini
				$rankList[] = $tmp['KODE_KARYAWAN'];
			}
			
			$data["$month-$year"] += $tmp['PERB_POINT']? $tmp['PERB_POINT'] : 0;	
		}
		
		//no data, normalize it...
		if (!$data["$month-$year"]) $data["$month-$year"] = 0;
		
		//2. devide it
		$jmlKaryawan = count($rankList);
		if ($jmlKaryawan) $data["$month-$year"] = $data["$month-$year"]/$jmlKaryawan;
	}
	return $data;
}

function __rep_rata_seluruh_poin2($startPeriode, $endPeriode){
	$result = mysql_query(
		"SELECT a.PERB_TGL, a.KODE_KARYAWAN, b.NAMA_KARYAWAN, e.NAMA_DEPARTMENT, a1.DETACHIEV_ID, 
				f.ACHIEV_JENIS, f.ACHIEV_BIDANG, g.DETACHIEV_TINGKAT, g.DETACHIEV_NAMA,
				a1.DEPERB_COUNT, 
					(SELECT NILAI_POINT 
					 FROM nilai as xx 
					 WHERE xx.DETACHIEV_ID = a1.DETACHIEV_ID AND 
							a1.DEPERB_COUNT BETWEEN xx.NILAI_MIN AND xx.NILAI_MAX
					) AS POINT
			FROM perbuatan as a, detil_perbuatan as a1, 
				data_karyawan as b, relasi_div_jab_din as c, 
				dep_divisi_jabatan as d, data_department as e,
				prestasipelanggaran as f, detil_prestasi_pelanggaran as g
			WHERE a.PERB_NO = a1.PERB_NO AND
				a1.DEPERB_STATUS='2' AND 
				a.KODE_KARYAWAN = b.KODE_KARYAWAN AND
				b.KODE_KARYAWAN  = c.KODE_KARYAWAN AND
				c.ID_DEP_DIV_JAB = d.ID_DEP_DIV_JAB AND
				d.ID_DEPARTMENT  = e.ID_DEPARTMENT AND
				f.ACHIEV_ID = g.ACHIEV_ID AND 
				g.DETACHIEV_ID = a1.DETACHIEV_ID AND
				DATE_FORMAT(a.PERB_TGL, '%Y-%m') BETWEEN DATE_FORMAT('$startPeriode', '%Y-%m') AND 
												DATE_FORMAT('$endPeriode', '%Y-%m')
		ORDER BY NAMA_DEPARTMENT, NAMA_KARYAWAN, ACHIEV_BIDANG, DEPERB_COUNT");
	
	//1. sum it
	$data = 0;
	$rankList = array();
	while ($tmp = mysql_fetch_assoc($result)){
		if (!in_array($tmp['KODE_KARYAWAN'], $rankList)) {
			$rankList[] = $tmp['KODE_KARYAWAN'];	
		}
		$data+= $tmp['POINT']? $tmp['POINT'] : 0;	
	}
	
	//no data, normalize it...
	if (!$data) $data = 0;
	
	//2. devide it
	$jmlKaryawan = count($rankList);
	if ($jmlKaryawan) $data= $data/$jmlKaryawan;
	
	return $data;
}


/**
 * Laporan Graph Reward Punishment Perguruan Tinggu (No.7)
 * Return jumlah Reward dan Punishment.
 * @param $startMonth
 * @param $endMonth
 * @param $year
 * @return (array)
 */
function __rep_countRewardPunishmentAll($startMonth, $endMonth, $year){
	$data = array();
	
	for($month=$startMonth; $month<=$endMonth; $month++){
		
		//hitung jumlah prestasi & pelanggaran
		$result = mysql_query(
				"SELECT a.KODE_KARYAWAN, d.DETACHIEV_ID, 
						c.ACHIEV_ID, c.ACHIEV_JENIS
				FROM perbuatan AS a, detil_perbuatan AS b, prestasipelanggaran AS c, 
					detil_prestasi_pelanggaran AS d
				WHERE a.PERB_NO = b.PERB_NO
					AND b.DEPERB_STATUS='2'
					AND b.DETACHIEV_ID = d.DETACHIEV_ID
					AND d.ACHIEV_ID = c.ACHIEV_ID
					AND year(a.PERB_TGL)='$year' AND month(a.PERB_TGL)='$month'");
		
		while ($tmp = mysql_fetch_assoc($result) ){
			if (strtolower($tmp['ACHIEV_JENIS']) === 'prestasi') 	$data["$month-$year"]['prestasi']++;
			if (strtolower($tmp['ACHIEV_JENIS']) === 'pelanggaran') $data["$month-$year"]['pelanggaran']++;
		}
		
		//normalize, klo ndak ada data
		if (!$data["$month-$year"]){
			$data["$month-$year"]['prestasi'] = 0;
			$data["$month-$year"]['pelanggaran'] = 0;	
		}
	}
	
	return $data;
}

//graph function
function __createLinePlot($data, $line, $mark=false, $value=false, $legend=false, $csim=false){
	$lineplot=new LinePlot($data);
	$lineplot->SetColor($line['color']);
	$lineplot->SetWeight($line['weight']);
	if ($mark){
		if ($mark['type']) 		$lineplot->mark->SetType($mark['type']);
		if ($mark['fillColor']) $lineplot->mark->SetFillColor($mark['fillColor']);
		if ($mark['width'])		$lineplot->mark->SetWidth($mark['width']);
	}
	if ($value){
		if ($value['color'])	$lineplot->value ->SetColor($value['color']);
		if ($value['font'][0] && $value['font'][1])		$lineplot ->value->SetFont($value['font'][0], $value['font'][1]); 
		if ($value['show']) 	$lineplot ->value->Show();
		if ($value['format']) 	$lineplot ->value->SetFormat($value['format']);
	}
	
	if ($legend){
		if ($legend['title'])	$lineplot->SetLegend($legend['title']);
	}
	
	if ($csim) {
		if ($csim['target'] && $csim['alt'])	$lineplot->SetCSIMTargets($csim['target'],$csim['alt']);
	}
	return $lineplot;
}